﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using AlexandriaPressAuthorObject;
using System.Transactions;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using MailSender;
using AlexandriaPressUserObject;

namespace AlexandriaPressAuthorDataAccessLayer
{
    public class DataLayerAuthor
    {
        private static string myConnectionString
        {
            get { return ConfigurationManager.ConnectionStrings["BookConn1"].ConnectionString; }
        }

        public static List<Book> authSearchMyBookByGenreOrTitle(string Title, int authorID)
        {

            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region SqlCommand

            SqlCommand comm = new SqlCommand();

            comm.Connection = conn;

            comm.CommandType = CommandType.StoredProcedure;

            comm.CommandText = "usp_View_All_Books_By_Genre";

            #endregion

            #region "Parameter"

            SqlParameter param1 = new SqlParameter();

            param1.DbType = DbType.String;

            param1.Value = Title;

            param1.ParameterName = "@string";

            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            SqlParameter param2 = new SqlParameter();

            param2.DbType = DbType.Int32;

            param2.Value = authorID;

            param2.ParameterName = "@authorID";

            param2.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param2);

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            #region "Execute command"

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            #endregion

            #region "Declaration"

            List<Book> lstOfBook = new List<Book>();

            Book book = null;

            #endregion

            #region "Read Data"

            while (dtReader.Read())
            {

                book = new Book();

                book.BookID = dtReader.GetInt32(dtReader.GetOrdinal("BookID"));

                book.AuthorID = dtReader.GetInt32(dtReader.GetOrdinal("AuthorID"));

                book.Title = dtReader.GetString(dtReader.GetOrdinal("Title"));

                book.Genre = dtReader.GetString(dtReader.GetOrdinal("Genre"));

                book.Stock = dtReader.GetInt32(dtReader.GetOrdinal("QuantityInStock"));

                book.DatePublished = dtReader.GetDateTime(dtReader.GetOrdinal("DatePublished"));

                book.Description = dtReader.GetString(dtReader.GetOrdinal("description"));

                book.BookCover = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                book.FileLocation = dtReader.GetString(dtReader.GetOrdinal("FileLocation"));

                lstOfBook.Add(book);

            }

            #endregion

            return lstOfBook;
        }

        public static Draft viewDraft(int draftID, int authorID)
        {
            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region SqlCommand

            SqlCommand comm = new SqlCommand();

            comm.Connection = conn;

            comm.CommandType = CommandType.StoredProcedure;

            comm.CommandText = "usp_View_Draft";

            #endregion

            #region "Parameter"

            SqlParameter param1 = new SqlParameter();

            param1.DbType = DbType.Int32;

            param1.Value = draftID;

            param1.ParameterName = "@draftID";

            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            SqlParameter param2 = new SqlParameter();

            param2.DbType = DbType.Int32;

            param2.Value = authorID;

            param2.ParameterName = "@authorID";

            param2.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param2);

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            #region "Execute command"

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            #endregion

            #region "Declaration"

            Draft draft = null;

            #endregion

            #region "Read Data"

            try
            {

                while (dtReader.Read())
                {

                    draft = new Draft();

                    draft.AuthorID = dtReader.GetInt32(dtReader.GetOrdinal("AuthorID"));

                    draft.DraftID = dtReader.GetInt32(dtReader.GetOrdinal("DraftID"));

                    draft.WorkingTitle = dtReader.GetString(dtReader.GetOrdinal("WorkingTitle"));

                    draft.Genre = dtReader.GetString(dtReader.GetOrdinal("Genre"));

                    draft.DateSubmitted = dtReader.GetDateTime(dtReader.GetOrdinal("DateSubmitted"));

                    if (!dtReader.IsDBNull(dtReader.GetOrdinal("ApprovalStatus")))
                        draft.ApprovalStatus = dtReader.GetString(dtReader.GetOrdinal("ApprovalStatus"));

                    draft.BookCoverLocation = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                    draft.FileLocation = dtReader.GetString(dtReader.GetOrdinal("FileLocation"));
                }
            }
            catch
            {
 
            }
            #endregion

            return draft;
        }

        public static string checkDraftTitle(string DraftTitle, int authorID)
        {
            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region SqlCommand

            SqlCommand comm = new SqlCommand();

            comm.Connection = conn;

            comm.CommandType = CommandType.StoredProcedure;

            comm.CommandText = "usp_View_Draft_Title_Exist";

            #endregion

            #region "Parameter"

            SqlParameter param1 = new SqlParameter();

            param1.DbType = DbType.String;

            param1.Value = DraftTitle;

            param1.ParameterName = "@workingTitle";

            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            SqlParameter param2 = new SqlParameter();

            param2.DbType = DbType.Int32;

            param2.Value = authorID;

            param2.ParameterName = "@authorID";

            param2.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param2);

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            #region "Execute command"

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            #endregion

            #region "Declaration"

            string draftTitle = null;

            #endregion

            try
            {
                #region "Read Data"

                dtReader.Read();


                if (!dtReader.IsDBNull(dtReader.GetOrdinal("WorkingTitle")))
                    draftTitle = dtReader.GetString(dtReader.GetOrdinal("WorkingTitle"));

                #endregion

            }
            catch
            {
                draftTitle = null;
            }
            return draftTitle;
        }

        public static Book authViewMyBook(int bookID, int authorID)
        {

            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region SqlCommand

            SqlCommand comm = new SqlCommand();

            comm.Connection = conn;

            comm.CommandType = CommandType.StoredProcedure;

            comm.CommandText = "usp_View_Book";

            #endregion

            #region "Parameter"

            SqlParameter param2 = new SqlParameter();

            param2.DbType = DbType.Int32;

            param2.Value = bookID;

            param2.ParameterName = "@bookID";

            param2.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param2);

            SqlParameter param3 = new SqlParameter();

            param3.DbType = DbType.Int32;

            param3.Value = authorID;

            param3.ParameterName = "@authorID";

            param3.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param3);

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            #region "Execute command"

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            #endregion

            #region "Declaration"

            Book book = null;

            #endregion

            #region "Read Data"

            while (dtReader.Read())
            {

                book = new Book();

                book.BookID = dtReader.GetInt32(dtReader.GetOrdinal("BookID"));

                book.AuthorID = dtReader.GetInt32(dtReader.GetOrdinal("AuthorID"));

                book.ApprovedDraftID = dtReader.GetInt32(dtReader.GetOrdinal("ApprovedDraftID"));

                book.Title = dtReader.GetString(dtReader.GetOrdinal("Title"));

                book.Genre = dtReader.GetString(dtReader.GetOrdinal("Genre"));

                book.Stock = dtReader.GetInt32(dtReader.GetOrdinal("QuantityInStock"));

                book.DatePublished = dtReader.GetDateTime(dtReader.GetOrdinal("DatePublished"));

                book.Description = dtReader.GetString(dtReader.GetOrdinal("description"));

                book.Price = Convert.ToInt32(dtReader.GetDecimal(dtReader.GetOrdinal("Price")));

                book.DatePriceWasSet = dtReader.GetDateTime(dtReader.GetOrdinal("DatePriceSet"));

                book.BookCover = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                book.FileLocation = dtReader.GetString(dtReader.GetOrdinal("FileLocation"));
            }

            #endregion

            return book;
        }

        public static List<Order> authBookSaleReport(string bookTitle, int AuthorID)
        {
            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region SqlCommand

            SqlCommand comm = new SqlCommand();

            comm.Connection = conn;

            comm.CommandType = CommandType.StoredProcedure;

            comm.CommandText = "usp_Get_Report_Sale_Of_Book";

            #endregion

            #region "Parameter"

            SqlParameter param1 = new SqlParameter();

            param1.DbType = DbType.String;

            param1.Value = bookTitle;

            param1.ParameterName = "@bookTitle";

            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            SqlParameter param2 = new SqlParameter();

            param2.DbType = DbType.Int32;

            param2.Value = AuthorID;

            param2.ParameterName = "@authorID";

            param2.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param2);

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            #region "Execute command"

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            #endregion

            #region "Declaration"

            List<Order> lstOfOrder = new List<Order>();

            Order order = null;

            #endregion

            #region "Read Data"

            try
            {

                while (dtReader.Read())
                {

                    order = new Order();

                    order.Price = Convert.ToInt32(dtReader.GetDecimal(dtReader.GetOrdinal("PricePerUnit")));

                    order.Quantity = dtReader.GetInt32(dtReader.GetOrdinal("QuantityInStock"));

                    order.BookTitle = dtReader.GetString(dtReader.GetOrdinal("Title"));

                    order.ImageLocation = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                    lstOfOrder.Add(order);

                }
            }
            catch
            {
 
            }

            #endregion

                return lstOfOrder;

        }

        public static List<Order> getOverAllSale(int authorID)
        {
            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region SqlCommand

            SqlCommand comm = new SqlCommand();

            comm.Connection = conn;

            comm.CommandType = CommandType.StoredProcedure;

            comm.CommandText = "usp_Get_Overall_Sale";

            #endregion

            #region "Parameter"

            SqlParameter param1 = new SqlParameter();

            param1.DbType = DbType.Int32;

            param1.Value = authorID;

            param1.ParameterName = "@authorID";

            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            #region "Execute command"

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            #endregion

            #region "Declaration"

            List<Order> lstOfOrder = new List<Order>();

            Order order = null;

            try
            {

            #endregion

                #region "Read Data"

                while (dtReader.Read())
                {
                    order = new Order();

                    order.Price = Convert.ToInt32(dtReader.GetDecimal(dtReader.GetOrdinal("PricePerUnit")));

                    order.Quantity = dtReader.GetInt32(dtReader.GetOrdinal("Quantity"));

                    order.BookTitle = dtReader.GetString(dtReader.GetOrdinal("Title"));

                    lstOfOrder.Add(order);

                }
                #endregion
            }
            catch
            {
 
            }
            return lstOfOrder;

        }

        public static Draft getImageAndFileLocation(int DraftID)
        {
            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region SqlCommand

            SqlCommand comm = new SqlCommand();

            comm.Connection = conn;

            comm.CommandType = CommandType.StoredProcedure;

            comm.CommandText = "usp_Get_Image_and_File_Location";

            #endregion

            #region "Parameter"

            SqlParameter param1 = new SqlParameter();

            param1.DbType = DbType.String;

            param1.Value = DraftID;

            param1.ParameterName = "@draftID";

            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            #region "Execute command"

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            #endregion

            #region "Declaration"

            Draft draft = null;

            #endregion

            #region "Read Data"

            while (dtReader.Read())
            {

                draft = new Draft();

                draft.BookCoverLocation = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                draft.FileLocation = dtReader.GetString(dtReader.GetOrdinal("FileLocation"));


            }

            #endregion

            return draft;
        }

        public static int submitDraft(string Title, string imageLocation, string genre, int authorID, string fileLocation,string userEmail,string username)
        {
            int i = 0;

            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            SqlTransaction trans = conn.BeginTransaction();

            try
            {

                #region "SqlCommand"

                SqlCommand comm = new SqlCommand();

                comm.Connection = conn;

                comm.CommandType = CommandType.StoredProcedure;

                comm.CommandText = "usp_Submit_Draft_Data";

                comm.Transaction = trans;

                #endregion

                #region "Parameters"

                SqlParameter param1 = new SqlParameter();

                param1.DbType = DbType.String;

                param1.Value = Title;

                param1.ParameterName = "@workingTitle";

                param1.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param1);

                SqlParameter param2 = new SqlParameter();

                param2.DbType = DbType.String;

                param2.Value = imageLocation;

                param2.ParameterName = "@bookCover";

                param2.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param2);

                SqlParameter param3 = new SqlParameter();

                param3.DbType = DbType.String;

                param3.Value = genre;

                param3.ParameterName = "@genre";

                param3.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param3);

                SqlParameter param4 = new SqlParameter();

                param4.DbType = DbType.Int32;

                param4.Value = authorID;

                param4.ParameterName = "@authorID";

                param4.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param4);

                SqlParameter param5 = new SqlParameter();

                param5.DbType = DbType.String;

                param5.Value = fileLocation;

                param5.ParameterName = "@fileLocation";

                param5.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param5);

                #endregion

                #region "Execute Command"

                comm.ExecuteScalar();

                #endregion

                #region "SqlCommand"

                SqlCommand comm1 = new SqlCommand();

                comm1.Connection = conn;

                comm1.CommandType = CommandType.StoredProcedure;

                comm1.CommandText = "usp_Get_EmailAdd_Of_All_ProofReaders";

                comm1.Transaction = trans;

                #endregion

                #region "Execute command"

                SqlDataReader dtReader = comm1.ExecuteReader();

                

                #endregion

                #region "Declaration"

                List<String> emails = new List<String>();

                string email = null;

                #endregion

                #region "Read Data"

                while (dtReader.Read())
                {
                    email = dtReader.GetString(dtReader.GetOrdinal("Email"));

                    emails.Add(email);
                }

                dtReader.Dispose();

                #endregion

                sendEmailNotifToProofReaderDraftSubmit(userEmail, emails, username);

                i = 1;

                trans.Commit();

            }
            catch
            {
                i = 0;
                trans.Rollback();
            }
            finally
            {

                trans.Dispose();

                #region "Closing Connection"

                conn.Close();

                #endregion
            }

            return i;
        }

        public static string getPath()
        {
            return "~/Drafts/";
        }

        public static string imagePath()
        {
            return "~/Images/";
        }

        public static int getBookID(string title)
        {
            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region "SqlCommand GetBookID"

            SqlCommand comm2 = new SqlCommand();

            comm2.Connection = conn;

            comm2.CommandType = CommandType.StoredProcedure;

            comm2.CommandText = "usp_Get_BookID";

            #endregion

            #region "Parameters"

            SqlParameter param11 = new SqlParameter();

            param11.DbType = DbType.String;

            param11.Value = title;

            param11.ParameterName = "@bookTitle";

            param11.Direction = ParameterDirection.Input;

            comm2.Parameters.Add(param11);

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            #region "Execute command"

            SqlDataReader dtReader = comm2.ExecuteReader(CommandBehavior.CloseConnection);

            #endregion

            #region "Declaration"

            Book book = new Book();

            #endregion

            #region "Read Data"

            dtReader.Read();

            book.BookID = dtReader.GetInt32(dtReader.GetOrdinal("BookID"));

            #endregion

            return book.BookID;

        }

        public static int publishDraftToSite(string Title, string imageLocation, string genre, int authorID, string fileLocation, string description, int draftID, int price, int stock)
        {
            int i = 0;

            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            SqlTransaction trans = conn.BeginTransaction();

            try
            {

                #region "SqlCommand SaveBookToDB"

                SqlCommand comm = new SqlCommand();

                comm.Connection = conn;

                comm.CommandType = CommandType.StoredProcedure;

                comm.CommandText = "usp_Publish_To_Site";

                comm.Transaction = trans;

                #endregion

                #region "Parameters"

                SqlParameter param1 = new SqlParameter();

                param1.DbType = DbType.String;

                param1.Value = Title;

                param1.ParameterName = "@title";

                param1.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param1);

                SqlParameter param2 = new SqlParameter();

                param2.DbType = DbType.String;

                param2.Value = imageLocation;

                param2.ParameterName = "@bookCover";

                param2.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param2);

                SqlParameter param3 = new SqlParameter();

                param3.DbType = DbType.String;

                param3.Value = genre;

                param3.ParameterName = "@genre";

                param3.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param3);

                SqlParameter param4 = new SqlParameter();

                param4.DbType = DbType.Int32;

                param4.Value = authorID;

                param4.ParameterName = "@authorID";

                param4.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param4);

                SqlParameter param5 = new SqlParameter();

                param5.DbType = DbType.String;

                param5.Value = fileLocation;

                param5.ParameterName = "@fileLocation";

                param5.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param5);

                SqlParameter param6 = new SqlParameter();

                param6.DbType = DbType.String;

                param6.Value = description;

                param6.ParameterName = "@description";

                param6.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param6);

                SqlParameter param7 = new SqlParameter();

                param7.DbType = DbType.Int32;

                param7.Value = draftID;

                param7.ParameterName = "@draftID";

                param7.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param7);

                SqlParameter param8 = new SqlParameter();

                param8.DbType = DbType.Int32;

                param8.Value = stock;

                param8.ParameterName = "@stock";

                param8.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param8);

                #endregion

                #region "Execute Command"

                comm.ExecuteScalar();

                #endregion

                #region "SqlCommand Edit Draft To Published"

                SqlCommand comm3 = new SqlCommand();

                comm3.Connection = conn;

                comm3.CommandType = CommandType.StoredProcedure;

                comm3.CommandText = "usp_Edit_Draft_Table_To_Published";

                comm3.Transaction = trans;

                #endregion

                #region "Parameters"

                SqlParameter param15 = new SqlParameter();

                param15.DbType = DbType.Int32;

                param15.Value = draftID;

                param15.ParameterName = "@draftID";

                param15.Direction = ParameterDirection.Input;

                comm3.Parameters.Add(param15);

                #endregion

                #region "Execute Command"

                comm3.ExecuteNonQuery();

                #endregion

                #region "SqlCommand GetBookID"

                SqlCommand comm2 = new SqlCommand();

                comm2.Connection = conn;

                comm2.CommandType = CommandType.StoredProcedure;

                comm2.CommandText = "usp_Get_BookID";

                comm2.Transaction = trans;

                #endregion

                #region "Parameters"

                SqlParameter param11 = new SqlParameter();

                param11.DbType = DbType.String;

                param11.Value = Title;

                param11.ParameterName = "@bookTitle";

                param11.Direction = ParameterDirection.Input;

                comm2.Parameters.Add(param11);

                #endregion

                #region "Execute command"

                SqlDataReader dtReader = comm2.ExecuteReader();

                #endregion

                #region "Declaration"

                Book book = new Book();

                #endregion

                #region "Read Data"

                dtReader.Read();

                book.BookID = dtReader.GetInt32(dtReader.GetOrdinal("BookID"));

                #endregion

                dtReader.Dispose();

                #region "SqlCommand SetPrice"

                SqlCommand comm1 = new SqlCommand();

                comm1.Connection = conn;

                comm1.CommandType = CommandType.StoredProcedure;

                comm1.CommandText = "usp_Set_Price";

                comm1.Transaction = trans;

                #endregion

                #region "Parameters"

                SqlParameter param9 = new SqlParameter();

                param9.DbType = DbType.Int32;

                param9.Value = book.BookID;

                param9.ParameterName = "@bookID";

                param9.Direction = ParameterDirection.Input;

                comm1.Parameters.Add(param9);

                SqlParameter param10 = new SqlParameter();

                param10.DbType = DbType.Int32;

                param10.Value = price;

                param10.ParameterName = "@price";

                param10.Direction = ParameterDirection.Input;

                comm1.Parameters.Add(param10);

                #endregion

                #region "Execute Command"

                comm1.ExecuteScalar();

                #endregion

                trans.Commit();

                i = 1;
            }
            catch
            {
                trans.Rollback();
                i = 0;
            }
            finally
            {
                trans.Dispose();

                #region "Closing Connection"

                conn.Close();

                #endregion

            }
            return i;
        }

        public static bool editPrice(int bookID, int price)
        {
            bool validator = false;
            

            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region "SqlCommand"

            SqlCommand comm = new SqlCommand();

            comm.Connection = conn;

            comm.CommandType = CommandType.StoredProcedure;

            comm.CommandText = "usp_Change_Price";

            #endregion

            #region "Parameters"

            SqlParameter param1 = new SqlParameter();

            param1.DbType = DbType.Int32;

            param1.Value = bookID;

            param1.ParameterName = "@bookID";

            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            SqlParameter param2 = new SqlParameter();

            param2.DbType = DbType.Int32;

            param2.Value = price;

            param2.ParameterName = "@price";

            param2.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param2);

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            try
            {
                #region "Execute Command"

                comm.ExecuteNonQuery();

                #endregion

                validator = true;
            }
            catch
            {
                #region "Closing Connection"

                conn.Close();

                #endregion
            }

            return validator;


        }

        public static int getAuthorID(string username)
        {
            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region SqlCommand

            SqlCommand comm = new SqlCommand();

            comm.Connection = conn;

            comm.CommandType = CommandType.StoredProcedure;

            comm.CommandText = "usp_Get_Author_ID";

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            #region "Parameter"

            SqlParameter param2 = new SqlParameter();

            param2.DbType = DbType.String;

            param2.Value = username;

            param2.ParameterName = "@username";

            param2.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param2);

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            #region "Execute command"

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            #endregion

            #region "Declaration"

            Author auth = null;

            #endregion


                #region "Read Data"

                dtReader.Read();

                auth = new Author();

                auth.UserID = dtReader.GetInt32(dtReader.GetOrdinal("UserID"));

                #endregion

            return auth.UserID;
        }

        public static List<Order> getSaleReportByGenre(string genre, int AuthorID)
        {
            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region SqlCommand

            SqlCommand comm = new SqlCommand();

            comm.Connection = conn;

            comm.CommandType = CommandType.StoredProcedure;

            comm.CommandText = "usp_Get_Overall_Genre_Sale";

            #endregion

            #region "Parameter"

            SqlParameter param1 = new SqlParameter();

            param1.DbType = DbType.String;

            param1.Value = genre;

            param1.ParameterName = "@genre";

            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            SqlParameter param2 = new SqlParameter();

            param2.DbType = DbType.String;

            param2.Value = AuthorID;

            param2.ParameterName = "@authorID";

            param2.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param2);

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            #region "Execute command"

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            #endregion

            #region "Declaration"

            List<Order> lstOfOrder = new List<Order>();

            Order order = null;

            #endregion

            #region "Read Data"

            try
            {

                while (dtReader.Read())
                {

                    order = new Order();

                    order.Price = Convert.ToInt32(dtReader.GetDecimal(dtReader.GetOrdinal("PricePerUnit")));

                    order.Quantity = dtReader.GetInt32(dtReader.GetOrdinal("Quantity"));

                    order.BookTitle = dtReader.GetString(dtReader.GetOrdinal("Title"));

                    order.ImageLocation = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                    lstOfOrder.Add(order);

                }
            }
            catch
            {
 
            }

            #endregion

            return lstOfOrder;
        }

        public static List<Draft> viewApprovedDrafts(int authorID)
        {
            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region SqlCommand

            SqlCommand comm = new SqlCommand();

            comm.Connection = conn;

            comm.CommandType = CommandType.StoredProcedure;

            comm.CommandText = "usp_View_Approved_Books";

            #endregion

            #region "Parameter"

            SqlParameter param1 = new SqlParameter();

            param1.DbType = DbType.Int32;

            param1.Value = authorID;

            param1.ParameterName = "@authorID";

            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            #region "Execute command"

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            #endregion

            #region "Declaration"

            List<Draft> lstOfBook = new List<Draft>();

            Draft draft = null;

            #endregion

            #region "Read Data"

            while (dtReader.Read())
            {

                draft = new Draft();

                draft.AuthorID = dtReader.GetInt32(dtReader.GetOrdinal("AuthorID"));

                draft.DraftID = dtReader.GetInt32(dtReader.GetOrdinal("DraftID"));

                draft.WorkingTitle = dtReader.GetString(dtReader.GetOrdinal("WorkingTitle"));

                draft.Genre = dtReader.GetString(dtReader.GetOrdinal("Genre"));

                draft.DateSubmitted = dtReader.GetDateTime(dtReader.GetOrdinal("DateSubmitted"));

                if (!dtReader.IsDBNull(dtReader.GetOrdinal("Comments")))
                    draft.Comments = dtReader.GetString(dtReader.GetOrdinal("Comments"));

                if (!dtReader.IsDBNull(dtReader.GetOrdinal("ApprovalStatus")))
                    draft.ApprovalStatus = dtReader.GetString(dtReader.GetOrdinal("ApprovalStatus"));

                draft.BookCoverLocation = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                lstOfBook.Add(draft);
            }

            #endregion

            return lstOfBook;

        }

        public static List<Draft> viewAllDrafts(int authorID)
        {
            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region SqlCommand

            SqlCommand comm = new SqlCommand();

            comm.Connection = conn;

            comm.CommandType = CommandType.StoredProcedure;

            comm.CommandText = "[usp_View_All_Drafts]";

            #endregion

            #region "Parameter"

            SqlParameter param1 = new SqlParameter();

            param1.DbType = DbType.Int32;

            param1.Value = authorID;

            param1.ParameterName = "@authorID";

            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            #region "Execute command"

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            #endregion

            #region "Declaration"

            List<Draft> lstOfBook = new List<Draft>();

            Draft draft = null;

            #endregion

            #region "Read Data"

            while (dtReader.Read())
            {

                draft = new Draft();

                draft.AuthorID = dtReader.GetInt32(dtReader.GetOrdinal("AuthorID"));

                draft.DraftID = dtReader.GetInt32(dtReader.GetOrdinal("DraftID"));

                draft.WorkingTitle = dtReader.GetString(dtReader.GetOrdinal("WorkingTitle"));

                draft.Genre = dtReader.GetString(dtReader.GetOrdinal("Genre"));

                draft.DateSubmitted = dtReader.GetDateTime(dtReader.GetOrdinal("DateSubmitted"));

                if (!dtReader.IsDBNull(dtReader.GetOrdinal("Comments")))
                {
                    draft.Comments = dtReader.GetString(dtReader.GetOrdinal("Comments"));
                }

                if (!dtReader.IsDBNull(dtReader.GetOrdinal("ApprovalStatus")))
                {
                    draft.ApprovalStatus = dtReader.GetString(dtReader.GetOrdinal("ApprovalStatus"));
                }

                draft.BookCoverLocation = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                lstOfBook.Add(draft);
            }

            #endregion

            return lstOfBook;

        }

        public static int editDraft(string Title, string imageLocation, string genre, int authorID, string fileLocation, int draftID)
        {
            int i = 0;


            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            #region "SqlCommand"

                SqlCommand comm = new SqlCommand();

                comm.Connection = conn;

                comm.CommandType = CommandType.StoredProcedure;

                comm.CommandText = "usp_Edit_Draft";

                #endregion

            #region "Parameters"

                SqlParameter param1 = new SqlParameter();

                param1.DbType = DbType.String;

                param1.Value = Title;

                param1.ParameterName = "@title";

                param1.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param1);

                SqlParameter param2 = new SqlParameter();

                param2.DbType = DbType.String;

                param2.Value = imageLocation;

                param2.ParameterName = "@imageLocation";

                param2.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param2);

                SqlParameter param3 = new SqlParameter();

                param3.DbType = DbType.String;

                param3.Value = genre;

                param3.ParameterName = "@genre";

                param3.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param3);

                SqlParameter param4 = new SqlParameter();

                param4.DbType = DbType.Int32;

                param4.Value = authorID;

                param4.ParameterName = "@authorID";

                param4.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param4);

                SqlParameter param5 = new SqlParameter();

                param5.DbType = DbType.String;

                param5.Value = fileLocation;

                param5.ParameterName = "@fileLocation";

                param5.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param5);

                SqlParameter param8 = new SqlParameter();

                param8.DbType = DbType.Int32;

                param8.Value = draftID;

                param8.ParameterName = "@draftID";

                param8.Direction = ParameterDirection.Input;

                comm.Parameters.Add(param8);

                #endregion


            try
            {

             #region "Execute Command"

                    comm.ExecuteNonQuery();

                    #endregion


                    i = 1;
             }
             catch
             {

                    i = 0;

                }

                #region "Closing Connection"

                conn.Close();

                #endregion

                return i;
        }

        public static List<Draft> viewDraftsForEditing(int authorID)
        {
            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region SqlCommand

            SqlCommand comm = new SqlCommand();

            comm.Connection = conn;

            comm.CommandType = CommandType.StoredProcedure;

            comm.CommandText = "[usp_View_Draft_For_Editing]";

            #endregion

            #region "Parameter"

            SqlParameter param1 = new SqlParameter();

            param1.DbType = DbType.Int32;

            param1.Value = authorID;

            param1.ParameterName = "@authorID";

            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            #region "Execute command"

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            #endregion

            #region "Declaration"

            List<Draft> lstOfBook = new List<Draft>();

            Draft draft = null;

            #endregion

            #region "Read Data"

            while (dtReader.Read())
            {

                draft = new Draft();

                draft.DraftID = dtReader.GetInt32(dtReader.GetOrdinal("DraftID"));

                draft.WorkingTitle = dtReader.GetString(dtReader.GetOrdinal("WorkingTitle"));

                draft.Genre = dtReader.GetString(dtReader.GetOrdinal("Genre"));

                draft.DateSubmitted = dtReader.GetDateTime(dtReader.GetOrdinal("DateSubmitted"));

                if (!dtReader.IsDBNull(dtReader.GetOrdinal("Comments")))
                {
                    draft.Comments = dtReader.GetString(dtReader.GetOrdinal("Comments"));
                }

                if (!dtReader.IsDBNull(dtReader.GetOrdinal("ApprovalStatus")))
                {
                    draft.ApprovalStatus = dtReader.GetString(dtReader.GetOrdinal("ApprovalStatus"));
                }

                draft.BookCoverLocation = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                lstOfBook.Add(draft);
            }

            #endregion

            return lstOfBook;

        }

        public static string approvedDraftValidator(string draft)
        {
            if (draft == "Draft_Approved")
                return "Approved";
            else if (draft == "Draft_For_Editing")
                return "Needs to be edited.Check Comments";
            else if (draft == "Draft_Denied")
                return "Content Unapproved.";
            else if (draft == "Draft_For_Checking")
                return "Needs Approval";
            return "Invalid Status";
        }

        public static string validateUserTypeIfAuthor(int userID)
        {
            #region "SqlConnection"

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = myConnectionString;

            #endregion

            #region SqlCommand

            SqlCommand comm = new SqlCommand();

            comm.Connection = conn;

            comm.CommandType = CommandType.StoredProcedure;

            comm.CommandText = "[usp_Validate_User_Type_As_Author]";

            #endregion

            #region "Parameter"

            SqlParameter param1 = new SqlParameter();

            param1.DbType = DbType.Int32;

            param1.Value = userID;

            param1.ParameterName = "@userID";

            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            #endregion

            #region "Opening Connection"

            if (conn.State != ConnectionState.Open) conn.Open();

            #endregion

            #region "Execute command"

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            #endregion

            #region "Declaration"

            string userType = null;

            #endregion

            #region "Read Data"

            dtReader.Read();

            userType = dtReader.GetString(dtReader.GetOrdinal("UserType"));

            #endregion

            return userType;
        }

        public static string userAuthorType()
        {
            return "Type_Author";
        }

        private static void sendEmailNotifToProofReaderDraftSubmit(string userEmail,List<String> proofEmails,string username)
        {
            foreach (string email in proofEmails)
            {
                SendNotificationToProofReader.SendMailMessageToProofReaderDraftSubmitted(userEmail, email, username);
            }
            
        }

        private static void sendEmailNotifToProofReaderDraftEdit()
        { 
        }
    }
}
